package com.canbot.u05.activity.customvoice.tool;


import android.util.Log;

import com.canbot.u05.activity.customvoice.bean.CustomDialogExcelData;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * ExportExcelUtils
 *
 * @author Liu.Heng
 * @date 2019/4/29
 */

public class ExportExcelUtils {

        private static WritableCellFormat sWritableCellFormat14 = null;

        private static WritableCellFormat sWritableCellFormat10 = null;

        private static WritableCellFormat sWritableCellFormat12 = null;

        private final static String UTF8_ENCODING = "UTF-8";

        /**
         * 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
         */
        private static void format() {
                try {
                        WritableFont sWritableFont14 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
                        sWritableFont14.setColour(jxl.format.Colour.LIGHT_BLUE);
                        sWritableCellFormat14 = new WritableCellFormat(sWritableFont14);
                        sWritableCellFormat14.setAlignment(jxl.format.Alignment.CENTRE);
                        sWritableCellFormat14.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                        sWritableCellFormat14.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

                        WritableFont sWritableFont10 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
                        sWritableCellFormat10 = new WritableCellFormat(sWritableFont10);
                        sWritableCellFormat10.setAlignment(jxl.format.Alignment.CENTRE);
                        sWritableCellFormat10.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                        sWritableCellFormat10.setBackground(Colour.GRAY_25);

                        WritableFont sWritableFont12 = new WritableFont(WritableFont.ARIAL, 10);
                        sWritableCellFormat12 = new WritableCellFormat(sWritableFont12);
                        //对齐格式
                        sWritableCellFormat10.setAlignment(jxl.format.Alignment.CENTRE);
                        //设置边框
                        sWritableCellFormat12.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

                }
                catch (WriteException e) {
                        e.printStackTrace();
                }
        }

        /**
         * 初始化Excel
         *
         * @param fileName 导出excel存放的地址（目录）
         * @param colName  excel中包含的列名（可以有多个）
         */
        public static void initExcel(String fileName, String[] colName) {
                format();
                WritableWorkbook workbook = null;
                try {
                        File file = new File(fileName);
                        if (!file.exists()) {
                               final Boolean isNew = file.createNewFile();
                                Log.e("", String.valueOf(isNew));
                        }
                        workbook = Workbook.createWorkbook(file);
                        //设置表格的名字
                        WritableSheet sheet = workbook.createSheet("自定义对话数据", 0);
                        //创建标题栏
                        sheet.addCell(new Label(0, 0, fileName, sWritableCellFormat14));
                        for (int col = 0; col < colName.length; col++) {
                                sheet.addCell(new Label(col, 0, colName[col], sWritableCellFormat10));
                        }
                        //设置行高
                        sheet.setRowView(0, 340);
                        workbook.write();
                }
                catch (Exception e) {
                        e.printStackTrace();
                }
                finally {
                        if (workbook != null) {
                                try {
                                        workbook.close();
                                }
                                catch (Exception e) {
                                        e.printStackTrace();
                                }
                        }
                }
        }

        @SuppressWarnings("unchecked")
        public static <T> void writeObjListToExcel(List<T> objList, String fileName) {
                if (objList != null && objList.size() > 0) {
                        WritableWorkbook writeBook = null;
                        InputStream in = null;
                        try {
                                WorkbookSettings setEncode = new WorkbookSettings();
                                setEncode.setEncoding(UTF8_ENCODING);
                                in = new FileInputStream(new File(fileName));
                                Workbook workbook = Workbook.getWorkbook(in);
                                writeBook = Workbook.createWorkbook(new File(fileName), workbook);
                                WritableSheet sheet = writeBook.getSheet(0);

                                for (int j = 0; j < objList.size(); j++) {
                                        CustomDialogExcelData customDialogExcelData = (CustomDialogExcelData) objList.get(j);
                                        List<String> list = new ArrayList<>();
                                        list.add(customDialogExcelData.getQustion_str());
                                        list.add(customDialogExcelData.getTts_text());
                                        list.add(customDialogExcelData.getImage_path());

                                        for (int i = 0; i < list.size(); i++) {
                                                sheet.addCell(new Label(i, j + 1, list.get(i), sWritableCellFormat12));
                                                if (list.get(i).length() <= 4) {
                                                        //设置列宽
                                                        sheet.setColumnView(i, list.get(i).length() + 8);
                                                }
                                                else {
                                                        //设置列宽
                                                        sheet.setColumnView(i, list.get(i).length() + 5);
                                                }
                                        }
                                        //设置行高
                                        sheet.setRowView(j + 1, 350);
                                }

                                writeBook.write();
                        }
                        catch (Exception e) {
                                e.printStackTrace();
                        }
                        finally {
                                if (writeBook != null) {
                                        try {
                                                writeBook.close();
                                        }
                                        catch (Exception e) {
                                                e.printStackTrace();
                                        }

                                }
                                if (in != null) {
                                        try {
                                                in.close();
                                        }
                                        catch (IOException e) {
                                                e.printStackTrace();
                                        }
                                }
                        }

                }
        }
}